package com.zzzzzz.app.account.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.zzzzzz.app.account.NoSuchUserException;
import com.zzzzzz.app.account.model.bo.UserBo;
import com.zzzzzz.app.account.model.bo.UserRoleBo;
import com.zzzzzz.app.account.model.po.Role;
import com.zzzzzz.app.account.model.po.User;
import com.zzzzzz.common.util.Validator;
import com.zzzzzz.core.dao.BaseDao;
import com.zzzzzz.core.exception.SystemException;

@Repository
public class UserRoleDao extends BaseDao {

	private static final String ADD_ASSOCIATION = "INSERT INTO user_role(user_id, role_id) VALUES(?, ?)";

	private static final String REMOVE_ASSOCIATION = "DELETE FROM user_role WHERE user_id = ? AND role_id = ?";
	
	private static final String _NO_SUCH_USER_WITH_PRIMARY_KEY = "No User exists with the primary key ";
	
	private static final String DELETE_BY_ROLEID = "DELETE FROM user_role WHERE role_id = ?";
	
	public void addAssociation(long userId, long roleId) throws SystemException {
		try{
			update(ADD_ASSOCIATION, userId, roleId);
		}catch (Exception e) {
			throw processException(e);
		}
	}
	
	public void removeAssociation(long userId, long roleId) throws SystemException {
		try{
			update(REMOVE_ASSOCIATION, userId, roleId);
		}catch (Exception e) {
			throw processException(e);
		}
	}
	
	public void deleteByRoleId(long roleId) throws SystemException {
		try{
			update(DELETE_BY_ROLEID, roleId);
		}catch (Exception e) {
			throw processException(e);
		}
	}
	
	protected class UserBoRowMapper implements RowMapper<UserBo> {
		public UserBo mapRow(ResultSet rs, int rn) throws SQLException {
			UserBo userBo = null;
			User user = new User();
			user.setUserId(rs.getLong("user_id"));
			user.setUsername(rs.getString("username"));
			user.setEmail(rs.getString("email"));
			user.setStatus(rs.getInt("status_"));

			/**
			 * No required
			 * 
			user.setFirstName(rs.getString("first_name"));
			user.setLastName(rs.getString("last_name"));
			user.setPortrait(rs.getString("portrait"));
			user.setAboutMe(rs.getString("about_me"));
			user.setDob(rs.getDate("dob"));
			user.setCreateDate(rs.getTimestamp("create_date"));
			user.setWebsite(rs.getString("website"));
			user.setGender(rs.getString("gender"));
			userBo = new UserBo(user);
			
			 */
			userBo = new UserBo(user);
			return userBo;
		}
	}
	
	public int findCurrentRoleMembersCount(long roleId) throws SystemException {
		try{
			String sql = "SELECT COUNT(*) FROM user_role ur INNER JOIN user_ u ON ur.user_id = u.user_id WHERE ur.role_id = ?";
			return findInt(sql, roleId);
		}catch(Exception e) {
			throw processException(e);
		}
	}
	
	public List<UserBo> findCurrentRoleMembers(long roleId, boolean ascending, int start, int offset) throws SystemException {
		try{
			String sql = "SELECT u.user_id, u.username, u.email, u.status_ FROM user_ u INNER JOIN user_role ur ON u.user_id = ur.user_id " + 
				"WHERE ur.role_id = ? ORDER BY u.create_date ";

			if(ascending) {
				sql += "ASC ";
			}else {
				sql += "DESC ";
			}
			
			sql += "LIMIT ?, ?";
			
			return findList(sql, new UserBoRowMapper(), roleId, start, offset);
			
		}catch(Exception e) {
			throw processException(e);
		}
	}
	
	public int findAvailableRoleMembersCount(long roleId) throws SystemException {
		try{
			String sql = "SELECT COUNT(u.user_id) FROM  user_ u WHERE u.user_id NOT in " +
					"(SELECT ur.user_id FROM user_role ur WHERE ur.role_id = ?) " + 
					"AND ? in (SELECT role_id FROM role_)";

			return findInt(sql, roleId, roleId);
		}catch(Exception e) {
			throw processException(e);
		}
	}
	
	public List<UserBo> findAvailableRoleMembers(long roleId, boolean ascending, int start, int offset) throws SystemException {
		try{
			String sql = "SELECT u.user_id, u.username, u.email, u.status_ " + 
				"FROM  user_ u WHERE u.user_id NOT in (SELECT ur.user_id FROM user_role ur WHERE ur.role_id = ?) " + 
				"AND ? in (SELECT role_id FROM role_) ORDER BY u.create_date ";

			if(ascending) {
				sql += "ASC ";
			}else {
				sql += "DESC ";
			}
			
			sql += "LIMIT ?, ?";
			
			return findList(sql, new UserBoRowMapper(), roleId, roleId, start, offset);
			
		}catch(Exception e) {
			throw processException(e);
		}
	}
	
	public UserRoleBo findByUserId(Long userId) throws NoSuchUserException, SystemException {
		UserRoleBo userRoleBo = null;
		String sql = "SELECT u.*, r.* FROM user_ u LEFT JOIN user_role ur ON u.user_id = ur.user_id " +
				"LEFT JOIN role_ r ON ur.role_id = r.role_id WHERE u.user_id = ?";
		try{
			List<UserRoleBo> list =  jdbcTemplate.getJdbcOperations().query(sql, new UserExtractor(), userId);
			if(list != null && list.size() > 0){
				userRoleBo = list.get(0);
			}
		}catch(Exception e) {
			throw processException(e);
		}
		if(userRoleBo == null) {
			throw new NoSuchUserException(_NO_SUCH_USER_WITH_PRIMARY_KEY + userId);
		}
		return userRoleBo;
	}
	
	/**
	 * 
	 * Deal with the entire ResultSet
	 *
	 */
	protected class UserExtractor implements ResultSetExtractor<List<UserRoleBo>> {
		public List<UserRoleBo> extractData(ResultSet rs) throws SQLException,DataAccessException {
			Map<Long, UserRoleBo> map = new HashMap<Long, UserRoleBo>();
			UserRoleBo userRoleBo = null;
			while (rs.next()) {
				Long userId = rs.getLong("user_id");
				userRoleBo = map.get(userId);
				if (userRoleBo == null) {
					User user = new User();
					user.setUserId(rs.getLong("user_id"));
					user.setUsername(rs.getString("username"));
					user.setEmail(rs.getString("email"));
					user.setPassword(rs.getString("password"));
					user.setFirstName(rs.getString("first_name"));
					user.setLastName(rs.getString("last_name"));
					user.setPortrait(rs.getString("portrait"));
					user.setStatus(rs.getInt("status_"));
					user.setAboutMe(rs.getString("about_me"));
					user.setDob(rs.getDate("dob"));
					user.setCreateDate(rs.getTimestamp("create_date"));
					user.setWebsite(rs.getString("website"));
					user.setGender(rs.getString("gender"));
					userRoleBo = new UserRoleBo(user);
					map.put(userId, userRoleBo);
				}
				
				Long roleId = rs.getLong("role_id");
				if(Validator.isNotNull(roleId)) {
					Role role = new Role();
					role.setRoleId(roleId);
					role.setName(rs.getString("name"));
					role.setDescription(rs.getString("description"));
					userRoleBo.getRoleList().add(role);
				}
			}
			return new ArrayList<UserRoleBo>(map.values());
		}
	}
		
}
